{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 来做pandas的练习吧"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 框框框\n",
    "![01_table_dataframe.svg](https://pandas.pydata.org/pandas-docs/version/1.0.2/_images/01_table_dataframe.svg)\n",
    "> <mark>框框框</mark>，探索，清理和处理数据在Pandas中，数据表称为DataFrame，数据科学家**变数variables**通常放(竖着的列)column，**观察observations**通常放(横着的行)row\n",
    "\n",
    "再来一次！要记住哟！！\n",
    "- **变量variables**通常放(竖着的列)column\n",
    "- **观察值observations**通常放(横着的行)row\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 框框框的代码片语\n",
    "框框框的代码片语 (code snippets)，新手请认真记忆，**注意标点及缩进**\n",
    "\n",
    "```python\n",
    "框框 = pd.DataFrame ( {\n",
    "        \"变数X\": [\"观察X1\", \"观察X2\", \"观察X3\", \"观察X4\"],\n",
    "        \"变数Y\": [\"观察Y1\", \"观察Y2\", \"观察Y3\", \"观察Y4\"],\n",
    "        \"变数Z\": [\"观察Z1\", \"观察Z2\", \"观察Z3\", \"观察Z4\"],\n",
    "      } )```\n",
    "\n",
    "记得，像人类语言一样，说的清楚，人就可以读的比较清楚....\n",
    "\n",
    "* pd.DataFrame 的主流参数是字典\n",
    "* 该字典的键keys是由变数构成，相当於表格中一行行的标题\n",
    "* 该字典的值values是由观察的列表构成，相当於表格中一行行的数据\n",
    "* 表格真的要是表格, 该字典的每个观察的列表数量必需齐一\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd #调用pandas 不要忘记咯"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 框框的练习1 \n",
    "（A1）那就来画一个框吧\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>变数X</th>\n",
       "      <th>变数Y</th>\n",
       "      <th>变数Z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>观察X1</td>\n",
       "      <td>观察Y1</td>\n",
       "      <td>观察Z1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>观察X2</td>\n",
       "      <td>观察Y2</td>\n",
       "      <td>观察Z2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>观察X3</td>\n",
       "      <td>观察Y3</td>\n",
       "      <td>观察Z3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>观察X4</td>\n",
       "      <td>观察Y4</td>\n",
       "      <td>观察Z4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    变数X   变数Y   变数Z\n",
       "0  观察X1  观察Y1  观察Z1\n",
       "1  观察X2  观察Y2  观察Z2\n",
       "2  观察X3  观察Y3  观察Z3\n",
       "3  观察X4  观察Y4  观察Z4"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "框框 = pd.DataFrame ( {\n",
    "        \"变数X\": [\"观察X1\", \"观察X2\", \"观察X3\", \"观察X4\"],\n",
    "        \"变数Y\": [\"观察Y1\", \"观察Y2\", \"观察Y3\", \"观察Y4\"],\n",
    "        \"变数Z\": [\"观察Z1\", \"观察Z2\", \"观察Z3\", \"观察Z4\"],\n",
    "      } )\n",
    "框框"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>变数X</th>\n",
       "      <th>变数Y</th>\n",
       "      <th>变数Z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>观察X1</td>\n",
       "      <td>观察Y1</td>\n",
       "      <td>观察Z1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>观察X2</td>\n",
       "      <td>观察Y2</td>\n",
       "      <td>观察Z2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>观察X3</td>\n",
       "      <td>观察Y3</td>\n",
       "      <td>观察Z3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>观察X4</td>\n",
       "      <td>观察Y4</td>\n",
       "      <td>观察Z4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    变数X   变数Y   变数Z\n",
       "0  观察X1  观察Y1  观察Z1\n",
       "1  观察X2  观察Y2  观察Z2\n",
       "2  观察X3  观察Y3  观察Z3\n",
       "3  观察X4  观察Y4  观察Z4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#第二种 用字典来画框框\n",
    "字典 = {\n",
    "    \"变数X\": [\"观察X1\", \"观察X2\", \"观察X3\", \"观察X4\"],\n",
    "    \"变数Y\": [\"观察Y1\", \"观察Y2\", \"观察Y3\", \"观察Y4\"],\n",
    "    \"变数Z\": [\"观察Z1\", \"观察Z2\", \"观察Z3\", \"观察Z4\"],\n",
    "}\n",
    "框框 = pd.DataFrame (字典)\n",
    "框框"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    变数X   变数Y   变数Z\n",
      "0  观察X1  观察Y1  观察Z1\n",
      "1  观察X2  观察Y2  观察Z2\n",
      "2  观察X3  观察Y3  观察Z3\n",
      "3  观察X4  观察Y4  观察Z4\n"
     ]
    }
   ],
   "source": [
    "## 打印框框 print\n",
    "## 在ipynb用 print比较不上看，因为是文字text输出为主\n",
    "print(框框)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>变数X</th>\n",
       "      <th>变数Y</th>\n",
       "      <th>变数Z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>观察X1</td>\n",
       "      <td>观察Y1</td>\n",
       "      <td>观察Z1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>观察X2</td>\n",
       "      <td>观察Y2</td>\n",
       "      <td>观察Z2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>观察X3</td>\n",
       "      <td>观察Y3</td>\n",
       "      <td>观察Z3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>观察X4</td>\n",
       "      <td>观察Y4</td>\n",
       "      <td>观察Z4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    变数X   变数Y   变数Z\n",
       "0  观察X1  观察Y1  观察Z1\n",
       "1  观察X2  观察Y2  观察Z2\n",
       "2  观察X3  观察Y3  观察Z3\n",
       "3  观察X4  观察Y4  观察Z4"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## 用display来画框框\n",
    "# 在ipynb可用 display 比较上看\n",
    "\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "# 从 IPython.display 模块 导入使用 display和HTML\n",
    "display (框框)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 框框的练习2\n",
    "（A2）**框框框**(DataFrame)的取变数成 **系列** (Series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    观察X1\n",
       "1    观察X2\n",
       "2    观察X3\n",
       "3    观察X4\n",
       "Name: 变数X, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A2 Series\n",
    "# first slice\n",
    "# 来取出变量吧\n",
    "框框 [\"变数X\"]  # [] 像字典取值, 改值从列表升级为有**索引的序列**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['观察X1', '观察X2', '观察X3', '观察X4'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A2 Series\n",
    "# 来取出‘值‘吧\n",
    "框框 [\"变数X\"].values # 值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=4, step=1)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A2 Series\n",
    "# 框框的索引\n",
    "框框 [\"变数X\"].index # 索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "变数X    观察X4\n",
       "变数Y    观察Y4\n",
       "变数Z    观察Z4\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A2 DataFrame .loc[]\n",
    "# 用 .loc[] 取列, 相当于你取出所有变量的某一次观察值\n",
    "框框.loc[3]   # loc = location  row 取得是行哦！！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['观察X1', '观察X2', '观察X3', '观察X4']\n",
      "['观察X1', '观察X2', '观察X3', '观察X4']\n",
      "['观察X1', '观察X2', '观察X3', '观察X4']\n",
      "[0, 1, 2, 3]\n"
     ]
    }
   ],
   "source": [
    "# A2 Series to_list()\n",
    "# 降阶打击, 变列表的方法\n",
    "print ( 框框 [\"变数X\"].to_list() )    \n",
    "print ( list(框框 [\"变数X\"]) )\n",
    "print ( list(框框 [\"变数X\"].values) )\n",
    "print ( list(框框 [\"变数X\"].index) )\n",
    "\n",
    "# 区分读/写代码: 你可以挑一个你比较常用的\"说法\", 但你看到别人不同的说法时, 你需要知道是同一件事"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 框框的总结\n",
    "来看看图吧\n",
    "![01_table_dataframe.svg](https://pandas.pydata.org/pandas-docs/version/1.0.2/_images/01_table_dataframe.svg)\n",
    "1.对数据科学来说，表格数据的习惯是变数variables与观察observations\n",
    "  *  行 row 观察值\n",
    "  *  列 Column 变量\n",
    "\n",
    "2.保留后面的逗号结构会更加清晰\n",
    "\n",
    "3.框框框的字典取的是某个变数所有观察, 用.loc[] 取个次观察的所有变数\n",
    "  * .loc[] 取  观察, 行 row\n",
    "  * [\"\"] 取  变量, 列 column\n",
    "\n",
    "4.取出框内的某一个变量的值\n",
    "  * 框框 [\"变数X\"].values\n",
    "\n",
    "5.索引\n",
    "  * 框框 [\"变数X\"].index \n",
    "\n",
    "6.来做出一个新的列表吧\n",
    "  *  to_list()\n",
    "  \n",
    "<div class=\"emoticon\">😃😄😁</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读读读\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![02_io_readwrite.svg](https://pandas.pydata.org/pandas-docs/version/1.0.2/_images/02_io_readwrite.svg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 如何读数据\n",
    "  * 输入有  **read_叉叉**  方法  \n",
    "  * 输出有  **to_叉叉**  方法  \n",
    "  * 可配合多种数据做输入输出\n",
    "  * 片语法记忆：以常用之csv及xlsx输入输出为例\n",
    "     * 数据框.read_csv( 档名, 参数 ... )\n",
    "     * 数据框.read_excel( 档名, 参数 ... )\n",
    "     * 数据框.to_csv( 档名, 参数 ... )\n",
    "     * 数据框.to_excel( 档名, 参数 ... )\n",
    "  * 人类语言片语化，可组合\n",
    "     * 输入 数据框.read_支援的格式名\n",
    "     * 输出 数据框.to_支援的格式名"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读读读的代码片语\n",
    "读读读的代码片语 (code snippets)，新手请认真记忆，**注意标点及缩进**\n",
    "\n",
    "```python\n",
    "\n",
    "读到csv = pd.read_csv(\"路径档案名\", encoding=\"utf8\")\n",
    "读到tsv = pd.read_csv(\"路径档案名\", encoding=\"utf8\", sep=\"\\t\")\n",
    "读到excel = pd.read_excel(\"路径档案名\", encoding=\"utf8\", sheet_name=\"分页名称\")\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读读读的练习1 \n",
    "（B1）那就来试着读读数据吧"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>排名</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>估值（亿人民币）</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>行业</th>\n",
       "      <th>掌门人/创始人</th>\n",
       "      <th>成立年份</th>\n",
       "      <th>部分投资机构</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>Ant Financial</td>\n",
       "      <td>10000</td>\n",
       "      <td>中国</td>\n",
       "      <td>杭州</td>\n",
       "      <td>金融科技</td>\n",
       "      <td>井贤栋</td>\n",
       "      <td>2014</td>\n",
       "      <td>春华资本、中投海外、红杉资本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>字节跳动</td>\n",
       "      <td>Bytedance</td>\n",
       "      <td>5000</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>媒体和娱乐</td>\n",
       "      <td>张一鸣</td>\n",
       "      <td>2012</td>\n",
       "      <td>红杉资本、海纳亚洲、纪源资本、启明创投</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>滴滴出行</td>\n",
       "      <td>Didi Chuxing</td>\n",
       "      <td>3600</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>共享经济</td>\n",
       "      <td>程维</td>\n",
       "      <td>2012</td>\n",
       "      <td>腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Infor</td>\n",
       "      <td>Infor</td>\n",
       "      <td>3500</td>\n",
       "      <td>美国</td>\n",
       "      <td>纽约</td>\n",
       "      <td>云计算</td>\n",
       "      <td>Jim Schaper</td>\n",
       "      <td>2002</td>\n",
       "      <td>Golden Gate Capital, Koch Equity Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JUUL Labs</td>\n",
       "      <td>JUUL Labs</td>\n",
       "      <td>3400</td>\n",
       "      <td>美国</td>\n",
       "      <td>旧金山</td>\n",
       "      <td>消费品</td>\n",
       "      <td>Adam Bowen, James Monsees, Kevin Burns, Tim Da...</td>\n",
       "      <td>2015</td>\n",
       "      <td>M13, Timothy Davis, Evolution VC Partners, Tig...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   排名       企业名称   Company Name  估值（亿人民币）  国家   城市     行业  \\\n",
       "0   1       蚂蚁金服  Ant Financial     10000  中国   杭州   金融科技   \n",
       "1   2       字节跳动      Bytedance      5000  中国   北京  媒体和娱乐   \n",
       "2   3       滴滴出行   Didi Chuxing      3600  中国   北京   共享经济   \n",
       "3   4      Infor          Infor      3500  美国   纽约    云计算   \n",
       "4   5  JUUL Labs      JUUL Labs      3400  美国  旧金山    消费品   \n",
       "\n",
       "                                             掌门人/创始人  成立年份  \\\n",
       "0                                                井贤栋  2014   \n",
       "1                                                张一鸣  2012   \n",
       "2                                                 程维  2012   \n",
       "3                                        Jim Schaper  2002   \n",
       "4  Adam Bowen, James Monsees, Kevin Burns, Tim Da...  2015   \n",
       "\n",
       "                                              部分投资机构  \n",
       "0                                     春华资本、中投海外、红杉资本  \n",
       "1                                红杉资本、海纳亚洲、纪源资本、启明创投  \n",
       "2                             腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本  \n",
       "3       Golden Gate Capital, Koch Equity Development  \n",
       "4  M13, Timothy Davis, Evolution VC Partners, Tig...  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# B1试试读tsv文件吧 \n",
    "# 20春_pandas_week02_hurun_unicorn.tsv\n",
    "df = pd.read_csv(\"20春_pandas_week02_hurun_unicorn.tsv\", encoding=\"utf8\", sep=\"\\t\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>排名</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>估值（亿人民币）</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>行业</th>\n",
       "      <th>掌门人/创始人</th>\n",
       "      <th>成立年份</th>\n",
       "      <th>部分投资机构</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>Ant Financial</td>\n",
       "      <td>10000</td>\n",
       "      <td>中国</td>\n",
       "      <td>杭州</td>\n",
       "      <td>金融科技</td>\n",
       "      <td>井贤栋</td>\n",
       "      <td>2014</td>\n",
       "      <td>春华资本、中投海外、红杉资本</td>\n",
       "      <td>环杭州湾大湾区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>字节跳动</td>\n",
       "      <td>Bytedance</td>\n",
       "      <td>5000</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>媒体和娱乐</td>\n",
       "      <td>张一鸣</td>\n",
       "      <td>2012</td>\n",
       "      <td>红杉资本、海纳亚洲、纪源资本、启明创投</td>\n",
       "      <td>渤海大湾区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>滴滴出行</td>\n",
       "      <td>Didi Chuxing</td>\n",
       "      <td>3600</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>共享经济</td>\n",
       "      <td>程维</td>\n",
       "      <td>2012</td>\n",
       "      <td>腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本</td>\n",
       "      <td>渤海大湾区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>陆金所</td>\n",
       "      <td>Lufax</td>\n",
       "      <td>2700</td>\n",
       "      <td>中国</td>\n",
       "      <td>上海</td>\n",
       "      <td>金融科技</td>\n",
       "      <td>计葵生</td>\n",
       "      <td>2011</td>\n",
       "      <td>摩根士丹利、中银集团、国泰君安（香港）</td>\n",
       "      <td>环杭州湾大湾区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>微众银行</td>\n",
       "      <td>WeBank</td>\n",
       "      <td>1500</td>\n",
       "      <td>中国</td>\n",
       "      <td>深圳</td>\n",
       "      <td>金融科技</td>\n",
       "      <td>顾敏</td>\n",
       "      <td>2014</td>\n",
       "      <td>腾讯、华平投资、淡马锡</td>\n",
       "      <td>粤港澳大湾区</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   排名  企业名称   Company Name  估值（亿人民币）  国家  城市     行业 掌门人/创始人  成立年份  \\\n",
       "0   1  蚂蚁金服  Ant Financial     10000  中国  杭州   金融科技     井贤栋  2014   \n",
       "1   2  字节跳动      Bytedance      5000  中国  北京  媒体和娱乐     张一鸣  2012   \n",
       "2   3  滴滴出行   Didi Chuxing      3600  中国  北京   共享经济      程维  2012   \n",
       "3   6   陆金所          Lufax      2700  中国  上海   金融科技     计葵生  2011   \n",
       "4  11  微众银行         WeBank      1500  中国  深圳   金融科技      顾敏  2014   \n",
       "\n",
       "                   部分投资机构   region  \n",
       "0          春华资本、中投海外、红杉资本  环杭州湾大湾区  \n",
       "1     红杉资本、海纳亚洲、纪源资本、启明创投    渤海大湾区  \n",
       "2  腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本    渤海大湾区  \n",
       "3     摩根士丹利、中银集团、国泰君安（香港）  环杭州湾大湾区  \n",
       "4             腾讯、华平投资、淡马锡   粤港澳大湾区  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# B2试试读csv文件吧 \n",
    "# 20春_pandas_week02_hurun_unicorn_more.csv\n",
    "df = pd.read_csv(\"20春_pandas_week02_hurun_unicorn_more.csv\", encoding=\"utf8\", sep=\"\\t\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>排名</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>估值（亿人民币）</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>行业</th>\n",
       "      <th>掌门人/创始人</th>\n",
       "      <th>成立年份</th>\n",
       "      <th>部分投资机构</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>Ant Financial</td>\n",
       "      <td>10000</td>\n",
       "      <td>中国</td>\n",
       "      <td>杭州</td>\n",
       "      <td>金融科技</td>\n",
       "      <td>井贤栋</td>\n",
       "      <td>2014</td>\n",
       "      <td>春华资本、中投海外、红杉资本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>字节跳动</td>\n",
       "      <td>Bytedance</td>\n",
       "      <td>5000</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>媒体和娱乐</td>\n",
       "      <td>张一鸣</td>\n",
       "      <td>2012</td>\n",
       "      <td>红杉资本、海纳亚洲、纪源资本、启明创投</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>滴滴出行</td>\n",
       "      <td>Didi Chuxing</td>\n",
       "      <td>3600</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>共享经济</td>\n",
       "      <td>程维</td>\n",
       "      <td>2012</td>\n",
       "      <td>腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Infor</td>\n",
       "      <td>Infor</td>\n",
       "      <td>3500</td>\n",
       "      <td>美国</td>\n",
       "      <td>纽约</td>\n",
       "      <td>云计算</td>\n",
       "      <td>Jim Schaper</td>\n",
       "      <td>2002</td>\n",
       "      <td>Golden Gate Capital, Koch Equity Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JUUL Labs</td>\n",
       "      <td>JUUL Labs</td>\n",
       "      <td>3400</td>\n",
       "      <td>美国</td>\n",
       "      <td>旧金山</td>\n",
       "      <td>消费品</td>\n",
       "      <td>Adam Bowen, James Monsees, Kevin Burns, Tim Da...</td>\n",
       "      <td>2015</td>\n",
       "      <td>M13, Timothy Davis, Evolution VC Partners, Tig...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   排名       企业名称   Company Name  估值（亿人民币）  国家   城市     行业  \\\n",
       "0   1       蚂蚁金服  Ant Financial     10000  中国   杭州   金融科技   \n",
       "1   2       字节跳动      Bytedance      5000  中国   北京  媒体和娱乐   \n",
       "2   3       滴滴出行   Didi Chuxing      3600  中国   北京   共享经济   \n",
       "3   4      Infor          Infor      3500  美国   纽约    云计算   \n",
       "4   5  JUUL Labs      JUUL Labs      3400  美国  旧金山    消费品   \n",
       "\n",
       "                                             掌门人/创始人  成立年份  \\\n",
       "0                                                井贤栋  2014   \n",
       "1                                                张一鸣  2012   \n",
       "2                                                 程维  2012   \n",
       "3                                        Jim Schaper  2002   \n",
       "4  Adam Bowen, James Monsees, Kevin Burns, Tim Da...  2015   \n",
       "\n",
       "                                              部分投资机构  \n",
       "0                                     春华资本、中投海外、红杉资本  \n",
       "1                                红杉资本、海纳亚洲、纪源资本、启明创投  \n",
       "2                             腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本  \n",
       "3       Golden Gate Capital, Koch Equity Development  \n",
       "4  M13, Timothy Davis, Evolution VC Partners, Tig...  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# B3 试试读xlsx文件吧 \n",
    "# 20春_pandas_week02_hurun_unicorn.xlsx\n",
    "df = pd.read_excel(\"20春_pandas_week02_hurun_unicorn.xlsx\", encoding=\"utf8\", sheet_name=\"独角兽\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>排名</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>估值（亿人民币）</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>行业</th>\n",
       "      <th>掌门人/创始人</th>\n",
       "      <th>成立年份</th>\n",
       "      <th>部分投资机构</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>蚂蚁金服</td>\n",
       "      <td>Ant Financial</td>\n",
       "      <td>10000</td>\n",
       "      <td>中国</td>\n",
       "      <td>杭州</td>\n",
       "      <td>金融科技</td>\n",
       "      <td>井贤栋</td>\n",
       "      <td>2014</td>\n",
       "      <td>春华资本、中投海外、红杉资本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>字节跳动</td>\n",
       "      <td>Bytedance</td>\n",
       "      <td>5000</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>媒体和娱乐</td>\n",
       "      <td>张一鸣</td>\n",
       "      <td>2012</td>\n",
       "      <td>红杉资本、海纳亚洲、纪源资本、启明创投</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>滴滴出行</td>\n",
       "      <td>Didi Chuxing</td>\n",
       "      <td>3600</td>\n",
       "      <td>中国</td>\n",
       "      <td>北京</td>\n",
       "      <td>共享经济</td>\n",
       "      <td>程维</td>\n",
       "      <td>2012</td>\n",
       "      <td>腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Infor</td>\n",
       "      <td>Infor</td>\n",
       "      <td>3500</td>\n",
       "      <td>美国</td>\n",
       "      <td>纽约</td>\n",
       "      <td>云计算</td>\n",
       "      <td>Jim Schaper</td>\n",
       "      <td>2002</td>\n",
       "      <td>Golden Gate Capital, Koch Equity Development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>JUUL Labs</td>\n",
       "      <td>JUUL Labs</td>\n",
       "      <td>3400</td>\n",
       "      <td>美国</td>\n",
       "      <td>旧金山</td>\n",
       "      <td>消费品</td>\n",
       "      <td>Adam Bowen, James Monsees, Kevin Burns, Tim Da...</td>\n",
       "      <td>2015</td>\n",
       "      <td>M13, Timothy Davis, Evolution VC Partners, Tig...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   排名       企业名称   Company Name  估值（亿人民币）  国家   城市     行业  \\\n",
       "0   1       蚂蚁金服  Ant Financial     10000  中国   杭州   金融科技   \n",
       "1   2       字节跳动      Bytedance      5000  中国   北京  媒体和娱乐   \n",
       "2   3       滴滴出行   Didi Chuxing      3600  中国   北京   共享经济   \n",
       "3   4      Infor          Infor      3500  美国   纽约    云计算   \n",
       "4   5  JUUL Labs      JUUL Labs      3400  美国  旧金山    消费品   \n",
       "\n",
       "                                             掌门人/创始人  成立年份  \\\n",
       "0                                                井贤栋  2014   \n",
       "1                                                张一鸣  2012   \n",
       "2                                                 程维  2012   \n",
       "3                                        Jim Schaper  2002   \n",
       "4  Adam Bowen, James Monsees, Kevin Burns, Tim Da...  2015   \n",
       "\n",
       "                                              部分投资机构  \n",
       "0                                     春华资本、中投海外、红杉资本  \n",
       "1                                红杉资本、海纳亚洲、纪源资本、启明创投  \n",
       "2                             腾讯、阿里巴巴、红杉资本、经纬中国、纪源资本  \n",
       "3       Golden Gate Capital, Koch Equity Development  \n",
       "4  M13, Timothy Davis, Evolution VC Partners, Tig...  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# B4 读取前面的几个数据 （最多读前五个） \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 494 entries, 0 to 493\n",
      "Data columns (total 10 columns):\n",
      "排名              494 non-null int64\n",
      "企业名称            494 non-null object\n",
      "Company Name    494 non-null object\n",
      "估值（亿人民币）        494 non-null int64\n",
      "国家              494 non-null object\n",
      "城市              494 non-null object\n",
      "行业              494 non-null object\n",
      "掌门人/创始人         494 non-null object\n",
      "成立年份            494 non-null int64\n",
      "部分投资机构          494 non-null object\n",
      "dtypes: int64(3), object(7)\n",
      "memory usage: 38.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# B5 取出header信息\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# B6 #返回一个表示DataFrame维数的元组。\n",
    "df.shape()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# B7 统计数据 包括计数，平均值，标准差，最小值，最大值以及较低的百分位数 count，mean，std，min，25%，50%，75%，max。\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  转化成各种档\n",
    "# B8 df.to_html()\n",
    "# B9 df.to_markdown()\n",
    "# B10 df.to_json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# B11 df.to_dict()\n",
    "# B12 df.to_sql()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读读读的小结\n",
    "再来看看图吧\n",
    "![02_io_readwrite.svg](https://pandas.pydata.org/pandas-docs/version/1.0.2/_images/02_io_readwrite.svg)\n",
    "\n",
    "1.通过代码读取你想要的文件\n",
    "\n",
    "2.shape，markdown是有要求的注意哈\n",
    "\n",
    "3.也可以通过代码把它转换成你想要的文件\n",
    "<div class=\"emoticon\">😃😄😁</div>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " ## 切切切\n",
    "\n",
    "![03_subset_columns_rows.svg](https://pandas.pydata.org/pandas-docs/version/1.0.2/_images/03_subset_columns_rows.svg)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## 如何选择表格的子集？  \n",
    "\n",
    "切切切，**切片** (英文叫slice) 是数据科学家找突破点的重要工具，是她们的数据解剖刀...\n",
    "\n",
    "参考 CheatSheet \n",
    "* Subset Observations (Rows) 行\n",
    "* Subset Variables (Columns) 列"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  行子集\n",
    "```python\n",
    "\n",
    "df.loc()\n",
    "df.iloc()\n",
    "df.set_index()\n",
    "df.head(n)\n",
    "df.tail(n)\n",
    "df.nlargest(n, '变量')\n",
    "df.nsmallest(n, '变量')\n",
    "df[df.估值（亿人民币）> 10]\n",
    "```\n",
    "\n",
    "#### 列子集\n",
    "```python\n",
    "\n",
    "#  行子集\n",
    "df[['变量X','变量Y','变量Z']]\n",
    "df[['变量X']]\n",
    "df['变量X']\n",
    "```\n",
    "\n",
    "#### 列+行子集\n",
    "```python\n",
    "\n",
    "#  行子集\n",
    "df.loc[:,['变量X':'变量Z']]    # 注意中括号里的: 和 ,的使用\n",
    "df.iloc[:,[1,2,5]]\n",
    "df.loc[df['变量X']>10, ['变量X','变量Z'] ]   \n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 子集代码片语说明\n",
    "* 行子集\n",
    "\n",
    "|   代码  |                                                                           用途    |\n",
    "| ------- |                                                                           ------- |\n",
    "| df.loc[]|     只能使用标签索引，不能使用整数索引，通过便签索引切边进行筛选时，前闭后闭。    |\n",
    "|df.iloc[]|只能使用整数索引，不能使用标签索引，通过整数索引切边进行筛选时，前闭后开。|\n",
    "|df.set_index()|可以设置单索引和复合索引。|\n",
    "|df.head(n)|会将表格中的第一行看作列名，并默认输出之后的五行，在head后面的括号里面直接写你想要输出的行数。|\n",
    "|df.tail(n)|与head相反|\n",
    "|df.nlargest(n, '变量')|能一次看到最大的几行，而且不需要排序。可以自己设定要看到排名前几的数据|\n",
    "|df.nsmallest(n, '变量')|与nlargest正好相反，看最小的|\n",
    "|[df.估值（亿人民币）> 10]|用来筛选|\n",
    "\n",
    "* df.set_index()\n",
    "> 函数原型：DataFrame.set_index(keys, drop=True, append=False, inplace=False, verify_integrity=False)\n",
    "    \n",
    "    * 参数解释：\n",
    "        * keys：列标签或列标签/数组列表，需要设置为索引的列\n",
    "        * drop：默认为True，删除用作新索引的列\n",
    "        * append：默认为False，是否将列附加到现有索引\n",
    "        * inplace：默认为False，适当修改DataFrame(不要创建新对象)\n",
    "        * verify_integrity：默认为false，检查新索引的副本。否则，请将检查推迟到必要时进行。将其设置为false将提高该方法的性能。\n",
    "\n",
    "\n",
    "* 列子集\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "|   代码  |                                                                           用途    |\n",
    "| ------- |                                                                           ------- |\n",
    "| df[['变量X','变量Y','变量Z']] |得到是'变量X','变量Y','变量Z'包含列索引的DataFrame结构 |\n",
    "| df[['变量X']] |'变量X'包含列索引的DataFrame结构|\n",
    "|df['变量X'] | 得到的是不包含列索引的Series结构|\n",
    "|df.变量|不包含列索引的Series结构|\n",
    "\n",
    "\n",
    "* 行＋列子集\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    观察X1\n",
       "1    观察X2\n",
       "2    观察X3\n",
       "3    观察X4\n",
       "Name: 变数X, dtype: object"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "框框.变数X"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![03_subset_columns_rows.svg](https://pandas.pydata.org/pandas-docs/version/1.0.2/_images/03_subset_columns_rows.svg)\n",
    "### 切切切的小结\n",
    "<div class=\"emoticon\">😃😄😁</div>\n",
    "\n",
    "1. 选出你想要的列，行，组合成一个新的列表\n",
    "2. 这个冒号的意思是所有行，逗号表示行与列的区分，记住他们吧\n",
    "3. 嘻嘻暂时想不出来了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
